﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Data.Odbc;

namespace SqlBackUp
{
    public partial class SQL_BACKUP_RESTORE : Form
    {
        private static Server srvSql;
        string mlogin = "";
        string mPassword = "";
        public SQL_BACKUP_RESTORE()
        {
            InitializeComponent();
        }
        //thiet lap ket noi
        private void btnConnect_Click(object sender, EventArgs e)
        {
            try
            {
                if (cmbServer.SelectedItem != null && cmbServer.SelectedItem.ToString() != "")
                {

                    groupBox2.Enabled = true;
                    groupBox3.Enabled = true;
                    groupBox4.Enabled = true;

                    ServerConnection srvConn = new ServerConnection(cmbServer.Text.ToString());   //khoi tao mot ca the moi cua lop serverconnection voi ten cua ca the server duoc chi dinh

                    srvConn.LoginSecure = false;   //thiet lap thuoc tinh false co nghia la ket noi khong duoc thiet lap toi Server bang cahc dung Windows Authentication or SQL Server Authentication 

                    srvConn.Login = txtUsername.Text;  //thiet lap logon(dang nhap) de thiet lap ket noi toi ca the Server

                    srvConn.Password = txtPassword.Text;  //thiet lap password dang nhap(logon) de thiet lap mot ket noi voi Server khi dung quyen SQL Server Authentication
                    mlogin = txtUsername.Text;
                    mPassword = txtPassword.Text;
                    srvSql = new Server(srvConn);   //tao mot doi tuong Server dai dien cho mot ca the SQL Server
                    cmb_Database.Items.Clear();
                    lb_Table.Items.Clear();
                    lb_View.Items.Clear();
                    lb_Sp.Items.Clear();
                    //Database dai dien cho mot tap hop cua cac doi tuong Database, moi doi tuong database dai dien cho mot database dinh nghia tren SQL Server
                    foreach (Database dbServer in srvSql.Databases)   //duyet het danh sach cac co so du lieu co trong SQL Server vua moi duoc thiet lap
                    {

                        cmb_Database.Items.Add(dbServer.Name);

                    }
                    cmbDevice.Items.Clear();
                    //BackupDevice dai dien cho mot tap hop cac doi tuong BackupDevice ,moi doi tuong BackupDevice dai dien cho mot backup device  dinh nghia tren mot ca the SQL Server
                    foreach (BackupDevice bkDevice in srvSql.BackupDevices)
                    {
                        cmbDevice.Items.Add(bkDevice.Name);

                    }

                }
                else
                {
                    MessageBox.Show("Ban can chon ten server", "Thong Bao", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        private void SQL_BACKUP_RESTORE_Load(object sender, EventArgs e)
        {
            try
            {
                DataTable dtServers = SmoApplication.EnumAvailableSqlServers(true);   //liet ke danh sach cua SQL SERVER san co o may dia phuong

                if (dtServers.Rows.Count > 0)   //neu nhu co server ton tai
                {

                    foreach (DataRow drServer in dtServers.Rows)
                    {

                        cmbServer.Items.Add(drServer["Name"].ToString().Trim());    //them tung ten server trong cot Name vao combox

                    }
                }
                groupBox2.Enabled = false;
                groupBox3.Enabled = false;
                groupBox4.Enabled = false;
            }
            catch { MessageBox.Show(" Qua Trinh Load Bi Loi !"); }
        }
        //tao device
        private void btn_Device_Click(object sender, EventArgs e)
        {
            try
            {
                if (saveBackupDialog1.ShowDialog() == DialogResult.OK)
                { //sp_addumpdevice them mot backup device de thiet ke mot co so du lieu cua mot ca the cua  Microsoft SQL Server 2005 
                    srvSql.ConnectionContext.ExecuteScalar("sp_addumpdevice @devtype='disk', @logicalname='" + tb_DeviceName.Text + "', @physicalname='" + saveBackupDialog1.FileName + "'");
                }
                cmbDevice.Items.Clear();
                foreach (BackupDevice bkDevice in srvSql.BackupDevices)
                {
                    cmbDevice.Items.Add(bkDevice.Name);

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        //quan sat cac thuoc tinh cua device
        private void btn_Preview_Click(object sender, EventArgs e)
        {
            if (cmbDevice.Enabled == true)
            {

                try
                {
                    if (cmbDevice.SelectedItem != null && cmbDevice.SelectedItem.ToString() != "")
                    {
                        Database db = srvSql.Databases["msdb"];
                        //lay ra vi tri, ten database, ten Server,kieu device,vi tri vat ly cua device,ngay bat dau backup,ngay hoan tat backup tu hai bang backupset va backupmediafamilly noi co ten logical cho truoc
                        string Sqlcomm = @"SELECT     dbo.backupset.position as [vitri], dbo.backupset.database_name AS [Database], dbo.backupset.server_name AS Server, dbo.backupmediafamily.logical_device_name AS [Device Name], 
                      dbo.backupmediafamily.physical_device_name AS Location, dbo.backupset.backup_start_date AS [Start Date], dbo.backupset.backup_finish_date AS [Finish Date],dbo.backupset.type AS [Device Type],dbo.backupset.backup_size AS [Backup Size]FROM dbo.backupset INNER JOIN dbo.backupmediafamily 
                      ON dbo.backupset.media_set_id = dbo.backupmediafamily.media_set_id WHERE     (dbo.backupmediafamily.logical_device_name = '" + cmbDevice.SelectedItem.ToString() + "')";
                        if (Sqlcomm.Replace(" ", "").ToUpper().StartsWith("SELECT"))
                        {
                            DataSet ds = db.ExecuteWithResults(Sqlcomm);  //thuc hien cau lenh va tra va mot bang du lieu cho Dataset
                            dataGridView1.DataSource = ds.Tables[0];
                        }
                    }
                    else
                    {
                        MessageBox.Show("Chưa chọn Device", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }



            } //neu nhu combox device khong enable thi mo device tu duong dan va quan sat thuoc tinh cua chung
            else
            {
                if (openBackupDialog1.ShowDialog() == DialogResult.OK)
                {
                    try
                    {
                        Database db = srvSql.Databases["msdb"];

                        string Sqlcomm = @"SELECT dbo.backupset.database_name AS [Database], dbo.backupset.server_name AS Server, dbo.backupmediafamily.logical_device_name AS [Device Name], 
                      dbo.backupmediafamily.physical_device_name AS Location, dbo.backupset.backup_start_date AS [Start Date],dbo.backupset.backup_finish_date AS [Finish Date],dbo.backupset.type AS [Device Type],dbo.backupset.backup_size AS [Backup Size] FROM
                      dbo.backupset INNER JOIN dbo.backupmediafamily ON dbo.backupset.media_set_id = dbo.backupmediafamily.media_set_id WHERE     (dbo.backupmediafamily.physical_device_name = '" + openBackupDialog1.FileName + "')";
                        if (Sqlcomm.Replace(" ", "").ToUpper().StartsWith("SELECT"))
                        {
                            DataSet ds = db.ExecuteWithResults(Sqlcomm);
                            dataGridView1.DataSource = ds.Tables[0];
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }
            }

        }

        private void Resfresh_Click(object sender, EventArgs e)
        {

            try
            {
                if (cmbServer.SelectedItem != null && cmbServer.SelectedItem.ToString() != "")
                {

                    groupBox2.Enabled = true;
                    groupBox3.Enabled = true;
                    groupBox4.Enabled = true;

                    ServerConnection srvConn = new ServerConnection(cmbServer.Text.ToString());

                    srvConn.LoginSecure = false;

                    srvConn.Login = txtUsername.Text;

                    srvConn.Password = txtPassword.Text;

                    srvSql = new Server(srvConn);
                    cmb_Database.Items.Clear();
                    lb_Table.Items.Clear();
                    lb_View.Items.Clear();
                    lb_Sp.Items.Clear();

                    foreach (Database dbServer in srvSql.Databases)
                    {

                        cmb_Database.Items.Add(dbServer.Name);

                    }
                    cmbDevice.Items.Clear();
                    foreach (BackupDevice bkDevice in srvSql.BackupDevices)
                    {
                        cmbDevice.Items.Add(bkDevice.Name);

                    }

                }
                else
                {
                    MessageBox.Show("Ban can chon server truoc", "Thong Bao", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btn_Table_Click(object sender, EventArgs e)
        {
            try
            {
                if (srvSql != null)
                {
                    Database dtb = srvSql.Databases[cmb_Database.SelectedItem.ToString()];      //doi tuong database luu giu mot tap hop cac doi tuong trong co so du lieu
                    lb_Table.Items.Clear();

                    foreach (Microsoft.SqlServer.Management.Smo.Table tb in dtb.Tables)     //lay cac doi tuong table co trong doi tuong dtb
                    {
                        lb_Table.Items.Add(tb.Name);
                    }

                }
                else
                {

                    MessageBox.Show("Ban can thiet lap ket noi truoc.", "Thong Bao", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("loi Qua Trinh Lay Table "+ex.Message);
            }
        }

        private void btn_View_Click(object sender, EventArgs e)
        {

            try
            {
                if (srvSql != null)
                {
                    Database dtb = srvSql.Databases[cmb_Database.SelectedItem.ToString()];
                    lb_View.Items.Clear();


                    foreach (Microsoft.SqlServer.Management.Smo.View v in dtb.Views)    //lay cac doi tuong view co trong dtb
                    {

                        lb_View.Items.Add(v.Name);

                    }

                }
                else
                {

                    MessageBox.Show("Ban can thiet lap ket noi truoc.", "Thong Bao", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                }
            }
            catch { MessageBox.Show("Loi Qua Trinh Lay View "); }

        }

        private void btn_Sp_Click(object sender, EventArgs e)
        {
            try
            {
                if (srvSql != null)
                {
                    Database dtb = srvSql.Databases[cmb_Database.SelectedItem.ToString()];
                    lb_Sp.Items.Clear();

                    foreach (Microsoft.SqlServer.Management.Smo.StoredProcedure s in dtb.StoredProcedures)      //lay cac doi tuong sp co trong dtb
                    {
                        lb_Sp.Items.Add(s.Name);

                    }
                }
                else
                {

                    MessageBox.Show("Ban can thiet lap ket noi truoc.", "Thong Bao", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                }
            }
            catch { MessageBox.Show("Loi Qua Trinh Lay SP"); }
        }

        private void radioButton2_CheckedChanged(object sender, EventArgs e)
        {
            cmbDevice.Enabled = false;
        }

        private void radioButton1_CheckedChanged(object sender, EventArgs e)
        {
            cmbDevice.Enabled = true;
        }

        //tao restore
        private void button1_Click(object sender, EventArgs e)
        {
        try{
        OdbcConnection conn_temp = new OdbcConnection();
        string str_temp;
        string Database_name = cmb_Database.SelectedItem.ToString();	//ten co so du lieu
        str_temp = "DSN=tempdb;Uid=" + mlogin.Trim() + ";Pwd=" + mPassword + ";";
        conn_temp.ConnectionString = str_temp;
        conn_temp.Open();
        //Kill các process đang kết tới csdl
        OdbcCommand cmd_kill = new OdbcCommand();
        string str;
        str = "SELECT spid, loginame FROM master.dbo.sysprocesses  WHERE(dbid =(SELECT dbid FROM master.dbo.sysdatabases WHERE  name = '" + Database_name + "'))";
        OdbcCommand cmd_process = new OdbcCommand();
        cmd_process.Connection = conn_temp;
        cmd_process.CommandText = str;
        cmd_process.CommandType = CommandType.Text;
        //Lấy ds các spID đang kết nối tới
        OdbcDataReader myreader  = cmd_process.ExecuteReader();
        if(myreader.HasRows)
	    {
            int []arrID=new int[100]; // Giả sử tối đa có 101 kết nối
            String []arrLogin=new String[100];
            int i  = 0;
            while (myreader.Read())
		    {
                arrID[i] = myreader.GetInt32(0);    //dua vao mang cac spid la processes id
                arrLogin[i] = myreader.GetString(1);
                i = i + 1;
     		}
            int m_process = i;
            int j;
            myreader.Close();
            for(j=0;j<i;j++)
		     {
                cmd_kill = new OdbcCommand();
                cmd_kill.CommandType = CommandType.Text;
                cmd_kill.Connection = conn_temp;
                cmd_kill.CommandText = "Kill " +arrID[j];
                try{
                    cmd_kill.ExecuteNonQuery();
                    MessageBox.Show("Kill thanh cong");
		            }catch(Exception ex)
                    {
                        MessageBox.Show("Qua trinh kill ket noi bi loi "+ex.ToString());
                    }
             }
        }
        try{
            string Device_name="";
            string vitridevice = "";
            //lay ten va vi tri cua device theo kieu file
            if (cmbDevice.Enabled == false)
            {
                int temp=1;
                temp += dataGridView1.CurrentCell.RowIndex;
                Device_name = dataGridView1.Rows[temp-1].Cells[2].Value.ToString();
                vitridevice = temp.ToString();
            }
             //lay ten va vi tri trong comboxdevice
            else
            { 
                Device_name = cmbDevice.SelectedItem.ToString();
                vitridevice = dataGridView1.CurrentCell.Value.ToString();
            }
           
            OdbcCommand cmd_restore = new OdbcCommand();
            cmd_restore.Connection = conn_temp;
           // cmd_restore.CommandText=@"restore database " + cmb_Database.SelectedItem.ToString() + " from " + cmbDevice.SelectedItem.ToString() + " with file = " + dataGridView1.CurrentCell.Value.ToString();
            cmd_restore.CommandText = @"restore database " + cmb_Database.SelectedItem.ToString() + " from " + Device_name + " with file = " + vitridevice;
            cmd_restore.CommandType = CommandType.Text;
            cmd_restore.ExecuteNonQuery();
            progressBar1.Visible = true;
            progressBar1.Refresh();
            for (int i = progressBar1.Minimum; i <= progressBar1.Maximum; i++)
                            {
                                progressBar1.PerformStep();

                            }
            progressBar1.Visible = false;
            MessageBox.Show("Restore CSDL thành công.");
            conn_temp.Close();
	}catch(Exception ex) 
        {
        MessageBox.Show("Co loi xay ra trong qua trinh Restore"+ex.ToString());
    }
   }catch(Exception ex)
   {
       MessageBox.Show("Ban can dien day du thong tin " + ex.ToString());
   }
 }

        private void button_backup_Click(object sender, EventArgs e)
        {
            try
            {
                if (srvSql != null)  //khi co mot ca the SQL Server
                {

                    if (cmbDevice.Enabled == false)
                    {  //khi chon backup database la kieu file
                        if (saveBackupDialog1.ShowDialog() == DialogResult.OK)
                        {

                            Backup bkpDatabase = new Backup();

                            bkpDatabase.Action = BackupActionType.Database;   //thiet lap kieu cua Backup de thuc hien backup, kieu o day la Databse

                            bkpDatabase.Database = cmb_Database.SelectedItem.ToString();    //chon Database de backup vao do


                            BackupDeviceItem bkpDevice = new BackupDeviceItem(saveBackupDialog1.FileName, DeviceType.File);  //khoi tao mot ca the bkpDevice voi ten va lieu cua device

                            bkpDatabase.Devices.Add(bkpDevice);   //dua ca the backup vao trong device de chuan bi backup

                            bkpDatabase.SqlBackup(srvSql);   //thuc hien phep toan backup vua moi duoc them vao tren do
                            progressBar1.Visible = true;
                            for (int i = progressBar1.Minimum; i <= progressBar1.Maximum; i++)
                            {
                                progressBar1.PerformStep();

                            }
                            progressBar1.Visible = false;


                        }

                    } //khi chon backup database vao device da co trong combox device
                    else
                    {
                        string Database_name = cmb_Database.SelectedItem.ToString();	//ten co so du lieu
                        OdbcConnection conn = new OdbcConnection();
                        KetNoi kn = new KetNoi();
                        kn.HuyKetNoi();
                        string ServerName = cmbServer.SelectedItem.ToString();
                        string DataBaseName = cmb_Database.SelectedItem.ToString();
                        string serName = txtUsername.Text.ToString();
                        string Secret = txtPassword.Text.ToString();
                        conn = kn.ThietlapketNoi(ServerName, DataBaseName, serName, Secret);

                        OdbcCommand cmd_bk = new OdbcCommand();
                        cmd_bk.Connection = conn;
                        cmd_bk.CommandText = @"backup database " + cmb_Database.SelectedItem.ToString() + " to " + cmbDevice.SelectedItem.ToString();
                        cmd_bk.CommandType = CommandType.Text;

                        try
                        {
                            cmd_bk.ExecuteNonQuery();
                            progressBar1.Visible = true;
                            progressBar1.Refresh();
                            for (int i = progressBar1.Minimum; i <= progressBar1.Maximum; i++)
                            {
                                progressBar1.PerformStep();

                            }
                            progressBar1.Visible = false;
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("Co loi trong qua trinh backup " + ex.ToString());
                        }
                        kn.HuyKetNoi();
                    }
                }
                else
                {

                    MessageBox.Show("Ket noi chua duoc thiet lap toi sql server", "Thong Bao", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                }
            }
            catch (Exception ex) { MessageBox.Show("Ban Can Dien Day Du Thong Tin " + ex.ToString()); }
        }

        private void button_taoketnoi_Click(object sender, EventArgs e)
        {
            try
            {
                string Database_name = cmb_Database.SelectedItem.ToString();	//ten co so du lieu
                OdbcConnection conn = new OdbcConnection();
                KetNoi kn = new KetNoi();
                kn.HuyKetNoi();
                string ServerName = cmbServer.SelectedItem.ToString();
                string DataBaseName = cmb_Database.SelectedItem.ToString();
                string serName = textBox_tendn.Text.ToString();
                string Secret = textBox_pass.Text.ToString();
                conn = kn.ThietlapketNoi(ServerName, DataBaseName, serName, Secret);
            }
            catch (Exception ex) { MessageBox.Show("Co loi khi tao ket noi "+ex.ToString()); }
        }
  }
}
